﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class ReceivablesObject
    {
        public const string sql = @"
SELECT DictionaryPartners.Name as Партнер,
DictionaryProject.Name as Проект,
Work1.CountWorks as Количество_работ,
Work1.SumDolg as Выполнено_работ_на_сумму,
DictionaryCurrencyWork.Name as Валюта_раб,
COALESCE(Transactions1.SumOplat,0) as Полученно_оплату_за_работы,
COALESCE(DictionaryCurrencyTransactions.Name,'---') as Валюта_опл,
COALESCE(Transactions1.SumOplat,0) - COALESCE(Work1.SumDolg,0) as Сальдо
FROM
		(SELECT [Work].[Customer]
			   ,[Work].[Project]
			   ,Sum([Work].[CountWork]) as CountWorks
			   ,Sum([Work].[PriceOfSales]) as SumDolg
			   ,[Work].[CurrencySales] as CurrencySales
		FROM   [Work]
		Group by [Work].[Customer], [Work].[Project], [Work].[CurrencySales]) as Work1
LEFT JOIN
		(SELECT [Transactions].[Partner]
			   ,[Transactions].[Project]
			   ,sum([Summa]) as SumOplat
			   ,[Transactions].[Currency] as Currency
		FROM   [Transactions]
		WHERE  [Transactions].[Type] = 1 and [Transactions].[Category] in (select Value from Settings where Code Like 'code_cat_dox_zak%')
		Group by [Transactions].[Partner], [Transactions].[Project], [Transactions].[Currency]) as Transactions1
ON Work1.Customer = Transactions1.Partner and Work1.Project = Transactions1.Project and Work1.CurrencySales = Transactions1.Currency
INNER JOIN DictionaryPartners on DictionaryPartners.ID = Work1.Customer
INNER JOIN DictionaryProject on DictionaryProject.ID = Work1.Project
INNER JOIN DictionaryCurrency as DictionaryCurrencyWork on DictionaryCurrencyWork.ID = Work1.CurrencySales
LEFT  JOIN DictionaryCurrency as DictionaryCurrencyTransactions on DictionaryCurrencyTransactions.ID = Transactions1.Currency
";

        public string Партнер { get; set; }
        public string Проект { get; set; }
        public int? Количество_работ { get; set; }
        public decimal? Выполнено_работ_на_сумму { get; set; }
        public string Валюта_раб { get; set; }
        public decimal? Полученно_оплату_за_работы { get; set; }
        public string Валюта_опл { get; set; }
        public decimal? Сальдо { get; set; }
    }
}
